IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsPackageExecutionLog' AND schema_id = 1 )
BEGIN
     DROP PROCEDURE dbo.InsPackageExecutionLog
END
GO

CREATE PROCEDURE dbo.InsPackageExecutionLog
     @PackageName                 nvarchar(255),
     @PackageExecutionGUID        varchar(38),
     @PackageMajorVersion         int,
     @PackageMinorVersion         int,
     @PackageBuildVersion         int,
     @MachineName                 nvarchar(128),
     @UserName                    nvarchar(128),
     @ContainerStartTime          datetime,
     @PackageStartTime            datetime,
     @PackageStartingFlag         tinyint       = 0, -- When both are zero it is 
     @PackageEndingFlag           tinyint       = 0, -- an info message.
     @ParentPackageExecutionId    bigint        = NULL,
     @ParentPackageExecutionIdStr varchar(30)   = NULL,
     @LogText                     varchar(6144) = NULL,
     @PackageExecutionId          bigint        = NULL OUTPUT,
     @PackageExecutionIdStr       varchar(30)   = NULL OUTPUT
AS
/*
 *
 * InsPackageExecutionLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON

     -- Handle case of PackageExecutionId
     IF ( @PackageExecutionId IS NULL AND @PackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @PackageExecutionId = CONVERT( bigint, @PackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
     END
     
     -- Handle case of ParentPackageExecutionId
     IF ( @ParentPackageExecutionId IS NULL AND @ParentPackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @ParentPackageExecutionId = CONVERT( bigint, @ParentPackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @ParentPackageExecutionIdStr = CONVERT( varchar(30), @ParentPackageExecutionId )
     END

     -- Condition Flags
     SELECT @PackageStartingFlag = CASE WHEN @PackageStartingFlag NOT IN ( 0, 1 )
                                        THEN 0
                                        ELSE @PackageStartingFlag
                                   END,
            @PackageEndingFlag   = CASE WHEN @PackageEndingFlag NOT IN ( 0, 1 )
                                        THEN 0
                                        ELSE @PackageEndingFlag
                                   END
     -- Handle Invalid flag combinations..
     SELECT @PackageStartingFlag = CASE WHEN @PackageStartingFlag = 1
                                         AND @PackageEndingFlag   = 1
                                        THEN 0
                                        ELSE @PackageStartingFlag
                                   END,
            @PackageEndingFlag   = CASE WHEN @PackageStartingFlag = 1
                                         AND @PackageEndingFlag   = 1
                                        THEN 0
                                        ELSE @PackageEndingFlag
                                   END

     -- Get the next package execution id from the SEQ table.  We do the an 
     -- optimistic commit with the expectation that the insert will succeed.
     -- given the way the insert occurs this is likely.  We do the commit 
     -- this way for concurrency reasons.  I don't wish to hold the lock any
     -- longer than I need to.
     IF ( @PackageStartingFlag = 1 )
     BEGIN
          BEGIN TRANSACTION

          UPDATE dbo.Seq 
          SET    SeqNumber = SeqNumber + 0 -- Lock record for a moment..
          WHERE  SeqName = 'PackageExecutionId'

          SELECT @PackageExecutionId = SeqNumber + 1
          FROM   dbo.Seq
          WHERE  SeqName = 'PackageExecutionId'
          
          UPDATE dbo.Seq
          SET   SeqNumber = @PackageExecutionId 
          FROM   dbo.Seq
          WHERE  SeqName = 'PackageExecutionId'

          COMMIT TRANSACTION
     END
     
     -- Insert it.
     INSERT dbo.EventLog 
          ( EventLogTypeId,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            MiscVC1,
            MiscVC2,
            MiscInt1,
            MiscInt2,
            MiscInt3,
            MiscBigInt1,
            MiscBigInt2,
            MiscDt1,
            MiscDt2,
            MiscDt3,
            MiscGUID4,
            MiscTinyInt1 )
     SELECT 1, -- EventLogTypeId
            'Integration Services', -- LogByApp
            'InsPackageExecutionLog', -- LogByAppSubSystem
            CONVERT( varchar(128), @PackageName ), -- LogByDesc
            LogText = CASE WHEN @PackageStartingFlag = 1
                            AND @PackageEndingFlag   = 0
                           THEN 'Package Starting'
                           WHEN @PackageStartingFlag = 0
                            AND @PackageEndingFlag   = 0
                           THEN @LogText 
                           WHEN @PackageStartingFlag = 0
                            AND @PackageEndingFlag   = 1
                           THEN 'Package Ending'
                           ELSE 'Package Action Unknown' -- Should not happen
                      END,
            CONVERT( varchar(128), @MachineName ), -- MiscVC1
            CONVERT( varchar(128), @UserName ), -- MiscVC2
            @PackageMajorVersion, -- MiscInt1
            @PackageMinorVersion, -- MiscInt2
            @PackageBuildVersion, -- MiscInt3
            MiscBigInt1 = @PackageExecutionId,
            MiscBigInt2 = CASE WHEN @ParentPackageExecutionId = -1
                               THEN NULL
                               ELSE @ParentPackageExecutionId
                          END, -- MiscBigInt2
            @ContainerStartTime, -- MiscDt1
            @PackageStartTime, -- MiscDt2
            MiscDt3 = CASE WHEN @PackageEndingFlag = 0
                           THEN NULL
                           WHEN @PackageEndingFlag = 1
                           THEN CURRENT_TIMESTAMP 
                           ELSE NULL -- Should not happen
                      END,
            @PackageExecutionGUID, -- MiscGUID4
            MiscTinyInt1 = CASE WHEN @PackageStartingFlag = 1
                                 AND @PackageEndingFlag   = 0
                                THEN 1 -- Start
                                WHEN @PackageStartingFlag = 0
                                 AND @PackageEndingFlag   = 0
                                THEN 128 -- Info Message
                                WHEN @PackageStartingFlag = 0
                                 AND @PackageEndingFlag   = 1
                                THEN 255 -- End
                                ELSE NULL -- Should not happen
                           END

     -- Get the value for the output variable and use it on the return
     SELECT @PackageExecutionId = CONVERT(bigint, @PackageExecutionId )
     SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
          
     -- Return it
     SELECT PackageExecutionId          = @PackageExecutionId,
            PackageExecutionIdStr       = @PackageExecutionIdStr,
            ParentPackageExecutionId    = @ParentPackageExecutionId,
            ParentPackageExecutionIdStr = @ParentPackageExecutionIdStr,
            PackageStartingFlag         = @PackageStartingFlag,
            PackageEndingFlag           = @PackageEndingFlag
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a SSIS package execution record into EventLog and returns a PackageExecutionId for the package to use.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsPackageExecutionLog;
GO
